﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;


namespace CallCenter.Data.BizUtilities
{
   public class JobTitleUtility:BaseUtility
    {
        public JobTitleUtility(DataManager manager)
            :base(manager)
        { }


        public ReturnMessage<JobTitleViewModel> InsertToJobTitle(JobTitleViewModel model)
        {
            var returnMessage = new ReturnMessage<JobTitleViewModel>();
            //  if(this.ValidateScratchPadModel(model))
            //   {
            var id = this.Insert(model.code, model.description);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;
            }
            return returnMessage;
        }

        public ReturnMessage<JobTitleViewModel> UpdateToJobTitle(JobTitleViewModel model)
        {
            var returnMessage = new ReturnMessage<JobTitleViewModel>();
            //  if(this.ValidateScratchPadModel(model))
            //   {
            var id = this.Update(model.code,model.description,model.id);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;
            }
            return returnMessage;
        }


        public List<JobTitleViewModel> GetAll() //Gets all the data form the contacts table
        {
            string sql = "Select * from ddlJobTitle order by description";

            return this.Factory.ExecuteObjects<JobTitleViewModel>(sql);
        }
        public int Insert(string code, string desription) //insert data to a contact table
        {
            int nRtn = -1;
         string sql = "INSERT INTO ddlJobTitle (code, description) Values(@code, @desc) SELECT @pResult =@@IDENTITY";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@code", SqlDbType.NVarChar, 10, code);
                this.Factory.AddParameter(cmd, "@desc", SqlDbType.NVarChar, 100, desription);

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;

        }
      
        public int Update(string code, string desription, int id) //insert data to a title table
        {
            int nRtn = -1;
            string sql = "UPDATE ddlJobTitle SET code = @code, description = @desc WHERE id =@id";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@code", SqlDbType.NVarChar, 10, code);
                this.Factory.AddParameter(cmd, "@desc", SqlDbType.NVarChar, 100, desription);

                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);

            }
            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }


        public void Delete(int titleId) //delete the row of data using the id passed to the function
        {
          string sql = "DELETE FROM ddlJobTitle " +
                "WHERE ID = @ID ";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, titleId);
                this.Factory.ExecuteNonQuery(cmd);
            }
          
        }

        public void DeleteAll() //delete all the rows in the data.
        {
          string sql = "DELETE FROM ddlJobTitle ";
            this.Factory.ExecuteTable(sql);

        }



        internal string GetJobtitleByJobId(int Jobid)
        {
            string nRtn = "None";
            string sql = "select @JobTitle = description from ddlJobTitle where id = @JobId";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@JobId", SqlDbType.Int, 500, Jobid);

                var param = this.Factory.AddParameter(cmd, "@JobTitle", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = param.Value.ToString();
            }
            return nRtn;
        }
    }
}
